package com.navi.mould1.service.impl;

import com.navi.bean.in.ConditionBean;
import com.navi.bean.out.kuosan_report.KuosanReportGridOut;
import com.navi.bean.sql.SqlVo;
import com.navi.mould1.service.IKuosanReportService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.stream.Collectors;

import static com.navi.bean.sql.SqlVo.jointInSql;
import static com.navi.core.util.JdbcUtils.mySql_QueryRunner_QueryListBySql;
import static com.navi.mould1.reportUtils.ComputeUtil.getAverageRate;
import static com.navi.mould1.reportUtils.ComputeUtil.getRate;
import static java.util.Comparator.comparing;
import static java.util.stream.Collectors.maxBy;
import static java.util.stream.Collectors.minBy;


@Slf4j
@Service
public class KuosanReportServiceImpl implements IKuosanReportService {


    public List<KuosanReportGridOut> getGridData(ConditionBean conditions) {
//        final SqlVo sqlVo = new SqlVo();
//        sqlVo.appendSql("SELECT");
//        sqlVo.appendSql("A.tool_id_fk,");
//        sqlVo.appendSql("B.tube_id_fk,");
//        sqlVo.appendSql("B.boat_id_fk,");
//        sqlVo.appendSql("A.rpt_timestamp,");
//        sqlVo.appendSql("A.data_value_001");
//        sqlVo.appendSql("FROM spc_diff.spc_ret_mfg_data_diff A");
//        sqlVo.appendSql("LEFT JOIN ret_diff_info B ON B.LINE_ID_FK = A.LINE_ID_FK AND A.RPT_TIMESTAMP >= B.START_TIMESTAMP AND A.RPT_TIMESTAMP < IFNULL(B.END_TIMESTAMP, NOW())");
//        sqlVo.appendSql("WHERE");
//        sqlVo.appendSql("A.MES_ID_FK = 'KSFZZD'");
//        if (StringUtils.isNotEmpty(conditions.getFab_id())) sqlVo.appendSql("AND A.FAB_ID_FK in" + jointInSql(conditions.getFab_id()));
//        if (StringUtils.isNotEmpty(conditions.getBay_id())) sqlVo.appendSql("AND A.LINE_ID_FK in" + jointInSql(conditions.getBay_id()));
//        if (StringUtils.isNotEmpty(conditions.getTool_main_id())) sqlVo.appendSql("AND A.TOOL_ID_FK in(select tool_id from bis_tool where root_tool_id in" + jointInSql(conditions.getTool_main_id()) + ")");
//        if (StringUtils.isNotEmpty(conditions.getTool_sub_id())) sqlVo.appendSql("AND A.TOOL_ID_FK in" + jointInSql(conditions.getTool_sub_id()));
//        if (StringUtils.isNotEmpty(conditions.getStart_time())) sqlVo.appendSqlAndAddParam("AND A.RPT_TIMESTAMP >= ? ", conditions.getStart_time());
//        if (StringUtils.isNotEmpty(conditions.getEnd_time())) sqlVo.appendSqlAndAddParam("AND A.RPT_TIMESTAMP <= ? ", conditions.getEnd_time());
//        sqlVo.appendSql("ORDER BY A.rpt_timestamp");

        final SqlVo sqlVo = new SqlVo();
        sqlVo.appendSql("SELECT");
        sqlVo.appendSql("A.tool_id_fk,");
        sqlVo.appendSql("B.data_ext as tube_id_fk,");
        sqlVo.appendSql("C.boat_id_fk,");
        sqlVo.appendSql("C.start_timestamp,");
        sqlVo.appendSql("C.end_timestamp,");
        sqlVo.appendSql("A.rpt_timestamp,");
        sqlVo.appendSql("A.data_value_001");
        sqlVo.appendSql("FROM spc_diff.spc_ret_mfg_data_diff A");
        sqlVo.appendSql("LEFT JOIN bis_data B ON B.DATA_CATE = 'TBMP' AND B.EXT_1 = A.TOOL_ID_FK");
        sqlVo.appendSql("LEFT JOIN ret_diff_info C ON C.LINE_ID_FK = A.LINE_ID_FK AND A.RPT_TIMESTAMP >= C.START_TIMESTAMP AND A.RPT_TIMESTAMP < C.END_TIMESTAMP");
        sqlVo.appendSql("WHERE");
        sqlVo.appendSql("A.MES_ID_FK = 'KSFZZD'");
        if (StringUtils.isNotEmpty(conditions.getFab_id())) sqlVo.appendSql("AND A.FAB_ID_FK in" + jointInSql(conditions.getFab_id()));
        if (StringUtils.isNotEmpty(conditions.getBay_id())) sqlVo.appendSql("AND A.LINE_ID_FK in" + jointInSql(conditions.getBay_id()));
        if (StringUtils.isNotEmpty(conditions.getTool_main_id())) sqlVo.appendSql("AND A.TOOL_ID_FK in(select tool_id from bis_tool where root_tool_id in" + jointInSql(conditions.getTool_main_id()) + ")");
        if (StringUtils.isNotEmpty(conditions.getTool_sub_id())) sqlVo.appendSql("AND A.TOOL_ID_FK in" + jointInSql(conditions.getTool_sub_id()));
        if (StringUtils.isNotEmpty(conditions.getStart_time())) sqlVo.appendSqlAndAddParam("AND A.RPT_TIMESTAMP >= ? ", conditions.getStart_time());
        if (StringUtils.isNotEmpty(conditions.getEnd_time())) sqlVo.appendSqlAndAddParam("AND A.RPT_TIMESTAMP <= ? ", conditions.getEnd_time());
        sqlVo.appendSql("ORDER BY A.rpt_timestamp ASC");

        List<KuosanReportGridOut> resultDatas = mySql_QueryRunner_QueryListBySql(sqlVo, KuosanReportGridOut.class);

        List<KuosanReportGridOut> final_gridOutList = new ArrayList<>();
        List<KuosanReportGridOut> gridOutList = new ArrayList<>();

        if (CollectionUtils.isNotEmpty(resultDatas) && resultDatas != null) {
            resultDatas.stream().collect(Collectors.groupingBy(data -> data.getTool_id_fk() + data.getTube_id_fk() + data.getBoat_id_fk() + data.getStart_timestamp())).forEach((key, list) -> {
                list = list.stream().filter(data -> data.getStart_timestamp() != null).sorted(Comparator.comparing(KuosanReportGridOut::getRpt_timestamp)).collect(Collectors.toList()); //以时间排序

                if (list.size() == 8 || list.size() == 16) {
                    List<KuosanReportGridOut> list2 = list.size() == 8 ? list : list.subList(0, 8);
                    KuosanReportGridOut gridOut = new KuosanReportGridOut();
                    gridOut.setTool_id_fk(list2.get(0).getTool_id_fk());
                    gridOut.setTube_id_fk(list2.get(0).getTube_id_fk());
                    gridOut.setBoat_id_fk(list2.get(0).getBoat_id_fk());
                    gridOut.setStart_timestamp(list2.get(0).getStart_timestamp());
                    gridOut.setEnd_timestamp(list2.get(0).getEnd_timestamp());
                    gridOut.setData_value_1(list2.get(0).getData_value_001());
                    gridOut.setData_value_2(list2.get(1).getData_value_001());
                    gridOut.setData_value_3(list2.get(2).getData_value_001());
                    gridOut.setData_value_4(list2.get(3).getData_value_001());
                    gridOut.setData_value_5(list2.get(4).getData_value_001());
                    gridOut.setData_value_6(list2.get(5).getData_value_001());
                    gridOut.setData_value_7(list2.get(6).getData_value_001());
                    gridOut.setData_value_8(list2.get(7).getData_value_001());
                    gridOut.setData_value_avg(String.valueOf(getAverageRate(list2.stream().mapToDouble(data -> Double.parseDouble(data.getData_value_001())).sum(), 8,2)));
                    gridOutList.add(gridOut);

                    if (list.size() == 16) {
                        list2 = list.subList(8, 16);
                        KuosanReportGridOut gridOut2 = new KuosanReportGridOut();
                        gridOut2.setTool_id_fk("未知");
                        gridOut2.setTube_id_fk("未知");
                        gridOut2.setBoat_id_fk("未知");
                        gridOut2.setStart_timestamp(list2.get(0).getStart_timestamp());
                        gridOut2.setEnd_timestamp(list2.get(0).getEnd_timestamp());
                        gridOut2.setData_value_1(list2.get(0).getData_value_001());
                        gridOut2.setData_value_2(list2.get(1).getData_value_001());
                        gridOut2.setData_value_3(list2.get(2).getData_value_001());
                        gridOut2.setData_value_4(list2.get(3).getData_value_001());
                        gridOut2.setData_value_5(list2.get(4).getData_value_001());
                        gridOut2.setData_value_6(list2.get(5).getData_value_001());
                        gridOut2.setData_value_7(list2.get(6).getData_value_001());
                        gridOut2.setData_value_8(list2.get(7).getData_value_001());
                        gridOut2.setData_value_avg(String.valueOf(getAverageRate(list2.stream().mapToDouble(data -> Double.parseDouble(data.getData_value_001())).sum(), 8,2)));
                        gridOutList.add(gridOut2);
                    }
                }
            });

            final_gridOutList = gridOutList.stream().sorted(Comparator.comparing(KuosanReportGridOut::getStart_timestamp)).collect(Collectors.toList());

            //取时间最大的最多3笔数据汇总平均值
            if (CollectionUtils.isNotEmpty(gridOutList) && gridOutList != null) {
                KuosanReportGridOut gridOutSumAvg = new KuosanReportGridOut();
                gridOutSumAvg.setTool_id_fk("-数据汇总-");
                gridOutSumAvg.setTube_id_fk("-数据汇总-");
                gridOutSumAvg.setBoat_id_fk("-数据汇总-");
                gridOutSumAvg.setData_value_avg(
                    String.valueOf(getAverageRate(
                            (gridOutList.size() > 3 ? gridOutList.stream().sorted(Comparator.comparing(KuosanReportGridOut::getStart_timestamp).reversed()).collect(Collectors.toList()).subList(0, 3) : gridOutList)
                                    .stream().mapToDouble(data -> Double.parseDouble(data.getData_value_avg())).sum(),
                            gridOutList.size() > 3 ? 3 : gridOutList.size(),
                            2
                    ))
                );
                final_gridOutList.add(gridOutSumAvg);
            }
        }
        return final_gridOutList;
    }

}
